{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## MySQLdb vs pymysql vs mysql.connector"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First install mysql-community software(refer the link mentioned below)<br>\n",
    "https://www.youtube.com/watch?v=WuBcTJnIuzo\n",
    "\n",
    "After that<br /> <code>pip install pymysql</code>\n",
    "\n",
    "or\n",
    "\n",
    "<code>pip install mysql.connector</code>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-03T09:12:14.909757Z",
     "start_time": "2018-07-03T09:12:14.864872Z"
    }
   },
   "outputs": [],
   "source": [
    "import pymysql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before moving forward, lets open <b>MySQL Command Line Client</b> and perform basic database operations:<br /><br />\n",
    "<code>show databases;</code><br /><br />\n",
    "<code>create database mydb;</code><br /><br />\n",
    "<code>use mydb;</code><br /><br />\n",
    "<code>show tables;</code><br /><br />\n",
    "<code>create table employees(eno int(5) primary key, ename varchar(10), eage int(3), eincome double(10,2));</code><br /><br />\n",
    "<code>desc employees;</code><br /><br />\n",
    "<code>select * from employees;</code><br /><br />\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-06-09T07:54:40.331235Z",
     "start_time": "2018-06-09T07:54:40.326237Z"
    }
   },
   "source": [
    "## How to connect with database in Python?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Import database module<br /><br />\n",
    "<code>import pymysql as pm</code><br /><br />or<br /><br /><code>import mysql.connector as pm</code><br /><br />\n",
    "\n",
    "2. Establish connection between python program and db<br /><br />\n",
    "<code>con = pm.connect(host, database, user, password)</code><br /><br />\n",
    "\n",
    "3. To execute mysql query and hold result cursor is required<br /><br />\n",
    "<code>cursor = con.cursor()</code><br /><br />\n",
    "\n",
    "4. Execute MySql query with the help of cursor object<br /><br />\n",
    "<code>cursor.execute(query)</code><br /><br />\n",
    "<code>cursor.executemany()</code><br /><br />\n",
    "\n",
    "5. fetch the result from cursor object in case of select query<br /><br />\n",
    "<code>cursor.fetchone()</code><br /><br />\n",
    "<code>cursor.fetchall()</code><br /><br />\n",
    "<code>cursor.fetchmany(n)</code><br /><br />\n",
    "\n",
    "6. commit or rollback changes based on your requirement<br /><br />\n",
    "<code>con.commit()</code><br /><br />\n",
    "<code>con.rollback()</code><br /><br />\n",
    "\n",
    "7. close the resources and disconnect database<br /><br />\n",
    "<code>cursor.close()</code><br /><br />\n",
    "<code>con.close()</code><br /><br />"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-03T09:29:06.813155Z",
     "start_time": "2018-07-03T09:29:06.799187Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<pymysql.connections.Connection object at 0x00780C50>\n",
      "DONE!!\n"
     ]
    }
   ],
   "source": [
    "import pymysql as pm\n",
    "\n",
    "try:\n",
    "    con = pm.connect(host='localhost', database='acadviewdb',\\\n",
    "                     user='root', password='root')\n",
    "    print(con)\n",
    "    \n",
    "finally:\n",
    "    con.close()\n",
    "    print('DONE!!')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-06-09T07:54:53.266358Z",
     "start_time": "2018-06-09T07:54:53.256400Z"
    }
   },
   "source": [
    "## Creating Database Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-03T09:37:08.641849Z",
     "start_time": "2018-07-03T09:37:08.123233Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table created successfully!!\n",
      "DONE!!\n"
     ]
    }
   ],
   "source": [
    "import pymysql as pm\n",
    "\n",
    "try:\n",
    "    con = pm.connect(host='localhost', database='acadviewdb',\\\n",
    "                     user='root', password='root')\n",
    "    \n",
    "    cursor = con.cursor()\n",
    "    \n",
    "    query = 'create table employees1(eno int(5) primary key, \\\n",
    "    ename varchar(10), eage int(3), eincome double(10,2), \\\n",
    "    foreign key(eno) references employees(eno))'\n",
    "    \n",
    "    cursor.execute(query)\n",
    "    \n",
    "    print('Table created successfully!!')\n",
    "    con.commit()\n",
    "    \n",
    "except pm.DatabaseError as e:\n",
    "    if con:\n",
    "        con.rollback()\n",
    "        print('Problem occured: ', e)\n",
    "    \n",
    "finally:\n",
    "    if cursor:\n",
    "        cursor.close()\n",
    "    if con:\n",
    "        con.close()\n",
    "    print('DONE!!')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-06-09T07:55:03.089437Z",
     "start_time": "2018-06-09T07:55:03.084439Z"
    }
   },
   "source": [
    "## Insert Operation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-03T09:42:53.032237Z",
     "start_time": "2018-07-03T09:42:53.006308Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Problem occured:  (1452, 'Cannot add or update a child row: a foreign key constraint fails (`acadviewdb`.`employees1`, CONSTRAINT `employees1_ibfk_1` FOREIGN KEY (`eno`) REFERENCES `employees` (`eno`))')\n",
      "DONE!!\n"
     ]
    }
   ],
   "source": [
    "import pymysql as pm\n",
    "\n",
    "try:\n",
    "    con = pm.connect(host='localhost', database='acadviewdb',\\\n",
    "                     user='root', password='root')\n",
    "    \n",
    "    cursor = con.cursor()\n",
    "    \n",
    "    query = \"insert into employees1(eno, ename, eage, eincome) \\\n",
    "    values(%s, %s, %s, %s)\"\n",
    "    \n",
    "    records = [(3, 'xyz', 33, 40000),(4, 'abc', 23, 23000)]\n",
    "    \n",
    "    cursor.executemany(query, records)\n",
    "    \n",
    "    con.commit()\n",
    "    \n",
    "except pm.DatabaseError as e:\n",
    "    if con:\n",
    "        con.rollback()\n",
    "        print('Problem occured: ', e)\n",
    "    \n",
    "finally:\n",
    "    if cursor:\n",
    "        cursor.close()\n",
    "    if con:\n",
    "        con.close()\n",
    "    print('DONE!!')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-06-09T07:55:13.283293Z",
     "start_time": "2018-06-09T07:55:13.273300Z"
    }
   },
   "source": [
    "## Read Operation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-03T06:51:23.965876Z",
     "start_time": "2018-07-03T06:51:23.951881Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Eno: 1, Ename: abc, Eage: 23, Esal: 23000.0\n",
      "DONE!!\n"
     ]
    }
   ],
   "source": [
    "import pymysql as pm\n",
    "\n",
    "try:\n",
    "    con = pm.connect(host='localhost', database='acadviewdb',\\\n",
    "                     user='root', password='root')\n",
    "    \n",
    "    cursor = con.cursor()\n",
    "    \n",
    "    query = 'select * from employees1'\n",
    "    \n",
    "    cursor.execute(query)\n",
    "    \n",
    "    data = cursor.fetchall()\n",
    "    \n",
    "    for row in data:\n",
    "        print('Eno: {}, Ename: {}, Eage: {}, Esal: {}'\\\n",
    "             .format(row[0], row[1], row[2], row[3]))\n",
    "    \n",
    "except pm.DatabaseError as e:\n",
    "    if con:\n",
    "        con.rollback()\n",
    "        print('Problem occured: ', e)\n",
    "    \n",
    "finally:\n",
    "    if cursor:\n",
    "        cursor.close()\n",
    "    if con:\n",
    "        con.close()\n",
    "    print('DONE!!')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-06-09T07:55:25.493274Z",
     "start_time": "2018-06-09T07:55:25.483243Z"
    }
   },
   "source": [
    "## Update Operation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-03T09:50:19.117113Z",
     "start_time": "2018-07-03T09:50:18.860763Z"
    },
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DONE!!\n"
     ]
    }
   ],
   "source": [
    "import pymysql as pm\n",
    "\n",
    "try:\n",
    "    con = pm.connect(host='localhost', database='acadviewdb',\\\n",
    "                     user='root', password='root')\n",
    "    \n",
    "    cursor = con.cursor()\n",
    "    \n",
    "    query = \"update employees1 set eage=eage+1 where ename = 'xyz'\"\n",
    "    \n",
    "    cursor.execute(query)\n",
    "    \n",
    "    con.commit()\n",
    "    \n",
    "except pm.DatabaseError as e:\n",
    "    if con:\n",
    "        con.rollback()\n",
    "        print('Problem occured: ', e)\n",
    "    \n",
    "finally:\n",
    "    if cursor:\n",
    "        cursor.close()\n",
    "    if con:\n",
    "        con.close()\n",
    "    print('DONE!!')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-06-09T07:55:36.169792Z",
     "start_time": "2018-06-09T07:55:36.164804Z"
    }
   },
   "source": [
    "## Delete Operation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-03T09:53:49.786400Z",
     "start_time": "2018-07-03T09:53:40.811483Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter age: 34\n",
      "DONE!!\n"
     ]
    }
   ],
   "source": [
    "import pymysql as pm\n",
    "\n",
    "try:\n",
    "    con = pm.connect(host='localhost', database='acadviewdb',\\\n",
    "                     user='root', password='root')\n",
    "    \n",
    "    cursor = con.cursor()\n",
    "    \n",
    "    age = input('Enter age: ')\n",
    "    \n",
    "    query = \"delete from employees1 where eage='%s'\"%(age)\n",
    "    \n",
    "    cursor.execute(query)\n",
    "    \n",
    "    con.commit()\n",
    "    \n",
    "except pm.DatabaseError as e:\n",
    "    if con:\n",
    "        con.rollback()\n",
    "        print('Problem occured: ', e)\n",
    "    \n",
    "finally:\n",
    "    if cursor:\n",
    "        cursor.close()\n",
    "    if con:\n",
    "        con.close()\n",
    "    print('DONE!!')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  },
  "toc": {
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
